package com.hnjowe.outputExcel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import com.union.core.workModel.model.ModelInfo;
import com.union.core.workModel.service.ModelInfoService;

public class NationLevel {
	private ModelInfo modelInfo=null;
	private String sex;
	private ModelInfoService modelService;
	public NationLevel(){
		modelService=new ModelInfoService();
	}
	public HSSFWorkbook createExcel(String[] ids){
		//定义单元格报头   
	    String worksheetTitle = "全国劳模\"三金\"发放情况统计表";   
	       
	    HSSFWorkbook wb = new HSSFWorkbook();   
	    //==================================================================   
	    // 创建单元格样式   
	    HSSFCellStyle cellStyleTitle = wb.createCellStyle();   
	    // 指定单元格居中对齐   
	    cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
	    // 指定单元格垂直居中对齐   
	    cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    //设边框
	    cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    // 指定当单元格内容显示不下时自动换行   
	    cellStyleTitle.setWrapText(true);   
	    // 设置单元格字体   
	    HSSFFont font = wb.createFont(); 
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    font.setFontName("宋体");   
	    font.setFontHeight((short) 280);   
	    cellStyleTitle.setFont(font);   
	    //------------------------------------------------------------------   
	    HSSFCellStyle cellStyle = wb.createCellStyle();   
	    // 指定单元格居中对齐   
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
	    // 指定单元格垂直居中对齐   
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
	    //设边框
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    // 指定当单元格内容显示不下时自动换行   
	    cellStyle.setWrapText(true);   
	    //------------------------------------------------------------------   
	    // 设置单元格字体   
	    HSSFFont font1 = wb.createFont();
	    font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	    font1.setFontName("宋体");   
	    font1.setFontHeight((short) 280);   
	    cellStyle.setFont(font1);   
	    	
	    if(null!=ids){
	    	for(String modelId:ids){
	    		//根据id查询劳模基本信息
	    		if(!"".equals(modelId)){
	    		int id=Integer.parseInt(modelId);
	    		modelInfo=modelService.queryModelById(id);
	    		sex=modelInfo.getModel_sex()==2?"女":"男";
	        //工作表名   
	        String worksheet = "劳模"+modelInfo.getModel_name()+"信息";   
	           
	        HSSFSheet sheet = wb.createSheet(worksheet);
	        //列宽
	        sheet.setColumnWidth((short)0, (short)3800);
	        sheet.setColumnWidth((short)1, (short)4500);
	        sheet.setColumnWidth((short)2, (short)5200);
	        sheet.setColumnWidth((short)3, (short)5500);
	        sheet.setColumnWidth((short)4, (short)5900);
	        sheet.setColumnWidth((short)5, (short)5900);
	        sheet.setColumnWidth((short)6, (short)5300);

//	      //图片
//	        String sDirPath = this.getServletConfig().getServletContext().getRealPath("/");
//	        System.out.println(sDirPath);
//	        ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
//	        BufferedImage bufferImg=ImageIO.read(new File(sDirPath+"/images/js.png"));
//	        ImageIO.write(bufferImg, "png", byteArrayOut);
//	        HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//	        HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,255,255,(short)7,5,(short)9,7);//7列5行开始
	        //插入图片
//	        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
//	                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
	        ExportExcel exportExcel = new ExportExcel(wb, sheet);   

	        // 创建报表头部   
	        exportExcel.createNormalHead(worksheetTitle, 6);   
	        HSSFRow row = null;   
	        HSSFCell cell = null;   
//	        for(int i=0; i<list.size(); i++){ 
            //定义第1行开始   
            row = sheet.createRow(1); 
            row.setHeight((short)350);
            cell = row.createCell((short)0);   
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)1);
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)2);
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)3); 
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)4); 
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)5); 
            cell.setCellValue(new HSSFRichTextString(""));   
            cell = row.createCell((short)6);
            cell.setCellValue(new HSSFRichTextString("单位：元"));   

            //定义第1行结束   
	      //定义第2行开始   
	            row = sheet.createRow(2); 
	            row.setHeight((short)700);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("序号"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("姓名"));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("发放年限"));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("春节慰问金额"));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("生活困难补助金"));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("特殊困难帮扶金"));   
	            cell = row.createCell((short)6);  
	            cell.setCellStyle(cellStyleTitle);
	            cell.setCellValue(new HSSFRichTextString("小计"));
	            //定义第2行结束   
	            int i;
	            for(i=0;i<5;i++){
	      	      //定义第2行开始   
		            row = sheet.createRow(i+3); 
		            row.setHeight((short)700);
		            cell = row.createCell((short)0);   
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""+i));   
		            cell = row.createCell((short)1);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)2);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)3); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)4); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)5); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)6);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            //定义第2行结束
	            }
 
	            //定义第i+3行开始
	            sheet.addMergedRegion(new Region(i+3, (short) 1, i+3, (short) 2));
	            row = sheet.createRow(i+3); 
	            row.setHeight((short)350);
	            cell = row.createCell((short)0);   
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString("合计"));   
	            cell = row.createCell((short)1);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellStyle(cellStyle);
	            cell.setCellValue(new HSSFRichTextString(""));   

	            //定义第i+3行结束  
	            //获取时间
//	            int y,m,d;    
//	            Calendar cal=Calendar.getInstance();    
//	            y=cal.get(Calendar.YEAR);    
//	            m=cal.get(Calendar.MONTH);    
//	            d=cal.get(Calendar.DATE);
	            //定义第i+3行开始   
	            row = sheet.createRow(i+4); 
	            row.setHeight((short)350);
	            cell = row.createCell((short)0);   
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)1);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellValue(new HSSFRichTextString("打印日期："));   
	            cell = row.createCell((short)6);
	            cell.setCellValue(new HSSFRichTextString("=TODAY()"));   
	            //定义第i+4行结束  
//	        }   
	    //}   
	            HSSFPrintSetup printSetup = sheet.getPrintSetup();
	            printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
	            printSetup.setScale((short)89);
	            printSetup.setLandscape(true);
	            sheet.setDisplayGridlines(false);
	            sheet.setPrintGridlines(false);
	            
	            sheet.setMargin(HSSFSheet.TopMargin,( double ) 1.0 ); // 上边距
	            sheet.setMargin(HSSFSheet.BottomMargin,( double ) 1.0 ); // 下边距
	            sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.8 ); // 左边距
	            sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.8 ); // 右边距
	     }
	    	}
	    	}else{
		        //工作表名   
		        String worksheet = "劳模三金信息";   
		        HSSFSheet sheet = wb.createSheet(worksheet);
		        //列宽
		        sheet.setColumnWidth((short)0, (short)3800);
		        sheet.setColumnWidth((short)1, (short)4500);
		        sheet.setColumnWidth((short)2, (short)5200);
		        sheet.setColumnWidth((short)3, (short)5500);
		        sheet.setColumnWidth((short)4, (short)5900);
		        sheet.setColumnWidth((short)5, (short)5900);
		        sheet.setColumnWidth((short)6, (short)5300);

//		      //图片
//		        String sDirPath = this.getServletConfig().getServletContext().getRealPath("/");
//		        System.out.println(sDirPath);
//		        ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
//		        BufferedImage bufferImg=ImageIO.read(new File(sDirPath+"/images/js.png"));
//		        ImageIO.write(bufferImg, "png", byteArrayOut);
//		        HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//		        HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,255,255,(short)7,5,(short)9,7);//7列5行开始
		        //插入图片
//		        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
//		                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
		        ExportExcel exportExcel = new ExportExcel(wb, sheet);   

		        // 创建报表头部   
		        exportExcel.createNormalHead(worksheetTitle, 6);   
		        HSSFRow row = null;   
		        HSSFCell cell = null;   
//		        for(int i=0; i<list.size(); i++){ 
	            //定义第1行开始   
	            row = sheet.createRow(1); 
	            row.setHeight((short)350);
	            cell = row.createCell((short)0);   
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)1);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)2);
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)3); 
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)4); 
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)5); 
	            cell.setCellValue(new HSSFRichTextString(""));   
	            cell = row.createCell((short)6);
	            cell.setCellValue(new HSSFRichTextString("单位：元"));   

	            //定义第1行结束   
		      //定义第2行开始   
		            row = sheet.createRow(2); 
		            row.setHeight((short)700);
		            cell = row.createCell((short)0);   
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("序号"));   
		            cell = row.createCell((short)1);
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("姓名"));   
		            cell = row.createCell((short)2);
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("发放年限"));   
		            cell = row.createCell((short)3); 
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("春节慰问金额"));   
		            cell = row.createCell((short)4); 
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("生活困难补助金"));   
		            cell = row.createCell((short)5); 
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("特殊困难帮扶金"));   
		            cell = row.createCell((short)6);  
		            cell.setCellStyle(cellStyleTitle);
		            cell.setCellValue(new HSSFRichTextString("小计"));
		            //定义第2行结束   
		            int i;
		            for(i=0;i<5;i++){
		      	      //定义第2行开始   
			            row = sheet.createRow(i+3); 
			            row.setHeight((short)700);
			            cell = row.createCell((short)0);   
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""+i));   
			            cell = row.createCell((short)1);
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            cell = row.createCell((short)2);
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            cell = row.createCell((short)3); 
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            cell = row.createCell((short)4); 
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            cell = row.createCell((short)5); 
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            cell = row.createCell((short)6);
			            cell.setCellStyle(cellStyle);
			            cell.setCellValue(new HSSFRichTextString(""));   
			            //定义第2行结束
		            }
	 
		            //定义第i+3行开始   
		            sheet.addMergedRegion(new Region(i+3, (short) 1, i+3, (short) 2));
		            row = sheet.createRow(i+3); 
		            row.setHeight((short)350);
		            cell = row.createCell((short)0);   
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString("合计"));   
		            cell = row.createCell((short)1);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)2);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)3); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)4); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)5); 
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)6);
		            cell.setCellStyle(cellStyle);
		            cell.setCellValue(new HSSFRichTextString(""));   

		            //定义第i+3行结束  
		            //获取时间
//		            int y,m,d;    
//		            Calendar cal=Calendar.getInstance();    
//		            y=cal.get(Calendar.YEAR);    
//		            m=cal.get(Calendar.MONTH);    
//		            d=cal.get(Calendar.DATE);
		            //定义第i+3行开始   
		            row = sheet.createRow(i+4); 
		            row.setHeight((short)350);
		            cell = row.createCell((short)0);   
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)1);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)2);
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)3); 
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)4); 
		            cell.setCellValue(new HSSFRichTextString(""));   
		            cell = row.createCell((short)5); 
		            cell.setCellValue(new HSSFRichTextString("打印日期："));   
		            cell = row.createCell((short)6);
		            cell.setCellValue(new HSSFRichTextString("=TODAY()"));   
		            //定义第i+4行结束  
//		        }   
		    //}   
		            HSSFPrintSetup printSetup = sheet.getPrintSetup();
		            printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
		            printSetup.setScale((short)89);
		            printSetup.setLandscape(true);
		            sheet.setDisplayGridlines(false);
		            sheet.setPrintGridlines(false);
		            
		            sheet.setMargin(HSSFSheet.TopMargin,( double ) 1.0 ); // 上边距
		            sheet.setMargin(HSSFSheet.BottomMargin,( double ) 1.0 ); // 下边距
		            sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.8 ); // 左边距
		            sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.8 ); // 右边距
	    	}
	     return wb;
	}
}
